PostgreSQL 命令速查

本篇把日常高频命令按「psql 元命令 / 常用 SQL / 备份恢复 / 诊断查询」四张表收拢，排障时直接翻。示例环境 Ubuntu 22.04 + PostgreSQL 16，数据目录按 /data/pgdata 写，路径换成自己的即可。

psql 元命令

连接之后所有反斜杠命令都走本地解析，不经过 SQL 引擎，响应很快。

元命令	作用	常用写法
`\l`	列出数据库	`\l+` 带大小和描述
`\c`	切换数据库	`\c app_db`
`\conninfo`	显示当前连接信息	排查连错库时先看它
`\dt`	列出表	`\dt public.*` 限定 schema
`\d`	查看表/视图/序列结构	`\d orders`
`\d+`	结构 + 大小 + 注释	`\d+ orders`
`\di`	列出索引	`\di+ idx_orders*`
`\dv`	列出视图	`\dv+`
`\dn`	列出 schema	`\dn+`
`\du`	列出角色和权限	`\du+`
`\df`	列出函数	`\df+ now`
`\dx`	列出已安装扩展	`\dx`
`\x`	切换纵向显示	宽表查询必备
`\timing`	开关执行耗时	`\timing on`
`\watch`	周期性重跑上一条查询	`\watch 5` 每 5 秒
`\e`	用编辑器写多行 SQL	复杂语句用它
`\i`	执行 SQL 文件	`\i /tmp/init.sql`
`\o`	结果输出到文件	`\o /tmp/out.txt`
`\copy`	客户端侧导入导出	`\copy t to '/tmp/t.csv' csv header`
`\encoding`	查看/设置客户端编码	`\encoding UTF8`
`\pset`	设置输出格式	`\pset format csv`
`\q`	退出	等价于 `\quit`

几个容易混的点：

\copy 是 psql 客户端命令，文件在客户端读写；COPY 是服务端 SQL，文件在数据库服务器上读写，且需要超级用户或 pg_read_server_files 角色。
\d 表名 不区分大小写，但表名有大小写时要用双引号，比如 \d "OrderItems"。
\watch 配合 \x 盯连接数、复制延迟很好用，按 Ctrl+C 退出。

常用 SQL

用途	SQL
当前库/用户	`SELECT current_database(), current_user;`
版本	`SELECT version();`
当前配置	`SHOW shared_buffers;`
所有配置	`SELECT name, setting, unit FROM pg_settings;`
库大小	`SELECT pg_size_pretty(pg_database_size('app_db'));`
表大小（含索引）	`SELECT pg_size_pretty(pg_total_relation_size('orders'));`
表与索引分开	`SELECT pg_size_pretty(pg_relation_size('orders')), pg_size_pretty(pg_indexes_size('orders'));`
建库	`CREATE DATABASE app_db OWNER app_user;`
建用户	`CREATE ROLE app_user LOGIN PASSWORD 'xxx';`
授权	`GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;`
刷权限	`SELECT pg_reload_conf();`
取消查询	`SELECT pg_cancel_backend(pid);`
断开连接	`SELECT pg_terminate_backend(pid);`
更新统计	`ANALYZE orders;`
手工清理	`VACUUM (VERBOSE, ANALYZE) orders;`
切换 WAL	`SELECT pg_switch_wal();`
当前 LSN	`SELECT pg_current_wal_lsn();`

建库建用户的完整顺序见安装与快速上手，这里只列单条。几个多行语句单独放：

-- 建库（template0 避免继承多余对象）
CREATE DATABASE app_db
  WITH OWNER = app_user
       ENCODING = 'UTF8'
       TEMPLATE = template0;

-- 新表默认授权，避免每次建表都补权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- 在线建索引，不阻塞写入
CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);

-- 查看某条 SQL 的执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

查看参数来源

SHOW 参数名 只看最终值，不知道是哪个文件设的。用下面这条能看来源：

SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE name = 'shared_buffers';

source 为 configuration file 是 postgresql.conf，为 database 是库级设置，为 client 是会话里 SET 的。

备份恢复

场景	命令	说明
单库逻辑备份	`pg_dump -Fc -d app_db -f app_db.dump`	自定义格式，支持并行恢复
单表备份	`pg_dump -Fc -t orders -d app_db -f orders.dump`	只导指定表
纯 SQL 备份	`pg_dump -Fp -d app_db -f app_db.sql`	可读，适合小库
全实例备份	`pg_dumpall -f all.sql`	含角色和表空间定义
只导角色	`pg_dumpall -g -f roles.sql`	迁移时保留账号
恢复自定义格式	`pg_restore -d app_db_new -j 4 app_db.dump`	`-j` 并行度
恢复单表	`pg_restore -d app_db -t orders app_db.dump`	目标表已存在会报错
恢复 SQL	`psql -d app_db -f app_db.sql`	纯 SQL 用这个
物理基础备份	`pg_basebackup -D /data/pgdata -Fp -Xs -P -R`	`-R` 自动配复制
搭备库	`pg_basebackup -h 主库 -U replicator -D /data/pgdata -Fp -Xs -P -R`	目标目录必须为空
文件系统备份起点	`SELECT pg_backup_start('snap1');`	快照方案配合使用
文件系统备份终点	`SELECT pg_backup_stop();`	拿到停止 LSN

PITR 的完整命令序列：

# 1. 还原「事前」的基础备份到数据目录（目录/tar/快照按实际格式处理，目录需为空）
sudo -u postgres rsync -a /backup/base/2024-06-01/ /data/pgdata/

# 2. 配恢复参数
sudo -u postgres tee /data/pgdata/postgresql.auto.conf <<'EOF'
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-06-01 15:30:00+08'
recovery_target_action = 'promote'
EOF

# 3. 触发恢复并启动
sudo -u postgres touch /data/pgdata/recovery.signal
sudo systemctl start postgresql@16-main

-- 恢复后确认
SELECT pg_is_in_recovery();          -- 期望 f
SELECT pg_current_wal_lsn();         -- 当前写入位置

备份的三条铁律

备份文件要和数据库分开存，同机同盘等于没备份。
定期做恢复演练，只跑 pg_dump 不验证恢复，出事时才发现备份是坏的。
PITR 需要连续 WAL，中间断一段就恢复不到目标时间，pg_stat_archiver.failed_count 要盯住。

诊断查询

连接与活动

-- 按状态统计连接
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY 2 DESC;

-- 当前活跃查询（排除自身）
SELECT pid, usename, datname, client_addr, application_name,
       now() - query_start AS duration, left(query, 100) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY duration DESC;

-- 超过 5 分钟的事务
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

-- 空闲事务
SELECT pid, application_name, now() - state_change AS idle_age, left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

锁与阻塞

-- 阻塞链：谁在等谁
SELECT blocked.pid AS blocked_pid,
       left(blocked.query, 80) AS blocked_query,
       blocking.pid AS blocking_pid,
       left(blocking.query, 80) AS blocking_query,
       now() - blocked.query_start AS wait_time
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY wait_time DESC;

-- 未授予的锁
SELECT pid, locktype, mode, relation::regclass, granted
FROM pg_locks
WHERE NOT granted;

-- 死锁累计次数
SELECT datname, deadlocks FROM pg_stat_database;

性能与膨胀

-- 缓存命中率（低于 95% 要关注）
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL;

-- 死元组最多的表
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

-- 事务 ID 年龄（超过 10 亿要紧急处理）
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

-- 索引使用情况（idx_scan 为 0 说明可能白建）
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC LIMIT 20;

-- 等待事件分布
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY 1, 2 ORDER BY 3 DESC;

复制与归档

-- 主库：备库状态与延迟
SELECT application_name, client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
       replay_lag
FROM pg_stat_replication;

-- 复制槽与保留的 WAL
SELECT slot_name, slot_type, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

-- WAL 目录总量
SELECT pg_size_pretty(sum(size)) AS wal_total FROM pg_ls_waldir();

-- 归档状态
SELECT archived_count, failed_count, last_archived_wal, last_failed_time
FROM pg_stat_archiver;

进度视图

长任务执行时用进度视图看进展，比干等强：

视图	看什么
`pg_stat_progress_vacuum`	VACUUM 进度、已处理页数
`pg_stat_progress_create_index`	建索引进度、当前阶段
`pg_stat_progress_copy`	COPY 已处理行数
`pg_stat_progress_cluster`	CLUSTER / VACUUM FULL 进度
`pg_stat_progress_basebackup`	基础备份进度

-- 建索引进度示例
SELECT pid, phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

统计视图的时效性

pg_stat_* 里的累计值来自统计系统（15 起改为共享内存统计），默认 track_counts = on 时会有秒级延迟，不是实时精确值。判断「现在有没有事」用 pg_stat_activity，判断「过去一段时间谁最耗资源」用 pg_stat_statements，两者配合看。

一句话速记

场景	先跑这个
连不上	`pg_lsclusters` + `pg_stat_activity` 连接数
慢	`pg_stat_statements` 按总耗时排序
卡住	`pg_blocking_pids()` 找阻塞链
膨胀	`pg_stat_user_tables.n_dead_tup`
复制延迟	`pg_stat_replication` 的 `replay_lag`
WAL 暴涨	`pg_replication_slots` + `pg_stat_archiver`
XID 回卷	`age(datfrozenxid)`

更完整的原理和处置思路见 PostgreSQL 核心概念与架构和 PostgreSQL 安装与快速上手。

psql 元命令​

常用 SQL​

备份恢复​

诊断查询​

连接与活动​

锁与阻塞​

性能与膨胀​

复制与归档​

进度视图​

一句话速记​